-- -------------------------------------------------
--    Project Name:  平台中断-揽收维度明细数据
--    File Name:     jms_dm.dm_cn_breakoff_detail_taking_dt
--    Description:   计算（桃花岛、紫金山、逍遥峰）中心-中心、中心-集散、中心-网点超过24、48、72、96、120小时数据
--    Author:        Joyce | 利玉仙
--    date:          2021-12-02
-- -------------------------------------------------
with t_taking as (
    select *
    from (
             select billcode
                  , scansitecode
                  , scantime
                  , row_number() over (partition by billcode order by scantime asc ) rnk
             from jms_ods.tab_barscan_taking
             where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
               and date_format(scantime, 'yyyy-MM-dd') between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and '{{ execution_date | date_add(-1) | cst_ds }}'
               and billcode like 'JT%'
         ) a
    where a.rnk = 1
),
     t_order_source as (
         select waybill_no billcode,input_time
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -20) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and date_format(input_time, 'yyyy-MM-dd') between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and '{{ execution_date | date_add(-1) | cst_ds }}'
           and ordersource_code in ('D67', 'D190', 'D09')--紫金山，逍遥峰，桃花岛
     )
insert overwrite table jms_dm.dm_cn_breakoff_detail_taking_dt partition ( dt )
select t_taking.billcode
     , t_taking.scantime                            as taking_time
     , t_order_source.input_time                    as read_time
     , t_break.network_code
     , t_break.network_name
     , t_break.network_type
     , t_break.agent_code
     , t_break.agent_name
     , t_break.franchisee_code
     , t_break.franchisee_name
     , t_break.order_source_code                    as order_source_code
     , t_break.order_source_name                    as order_source_name
     , t_break.breakoff_type
     , t_break.arrival_count
     , t_break.send_count
     , t_break.send_shipment
     , t_break.actual_departure_time
     , t_break.planned_departure_time
     , t_break.next_actual_arrival_time
     , t_break.next_planned_arrival_time
     , t_break.actual_trans_min
     , t_break.planned_trans_min
     , t_break.delay_trans_min
     , t_break.stop_min
     , t_break.stop_over_12
     , t_break.customer_code
     , t_break.customer_name
     , t_break.breakoff_scan_type
     , t_break.breakoff_scan_time
     , t_break.next_scan_code
     , t_break.next_scan_name
     , t_break.next_scan_type
     , t_break.next_agent_code
     , t_break.next_agent_name
     , t_break.next_franchisee_code
     , t_break.next_franchisee_name
     , t_break.next_est_scantype
     , t_break.next_est_scantime
     , t_break.end_code
     , t_break.end_name
     , t_break.breakoff_min
     , t_break.mage_region_code
     , t_break.mage_region_name
     , date_format(t_taking.scantime, 'yyyy-MM-dd') as dt
from t_taking t_taking
         join jms_dm.dm_cn_breakoff_detail_total_dt t_break
              on t_taking.billcode = t_break.billcode
                  and t_break.order_source_code in (1, 2, 3)
         left join t_order_source t_order_source
                   on t_taking.billcode = t_order_source.billcode
where t_break.dt = '{{ execution_date | date_add(-1) | cst_ds }}'
    
 distribute by pmod(hash(rand()), 63);
